package net.user.db.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class MemberDAO {
	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	public MemberDAO() {
		try{
			Context initCtx=new InitialContext();
			Context envCtx=(Context)initCtx.lookup("java:comp/env");
			DataSource ds=(DataSource)envCtx.lookup("jdbc/OracleDB");
			con=ds.getConnection();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
	public boolean insertMember(MemberBean mb) throws SQLException{
		String sql=null;
		
		try{
			sql="insert into member values"+"(?,?,?,?,?,?,?,?,?,?,?,?)";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, mb.getID());
			pstmt.setString(2, mb.getPW());
			pstmt.setString(3, mb.getNAME());
			pstmt.setInt(4, mb.getJUMIN1());
			pstmt.setInt(5, mb.getJUMIN2());
			pstmt.setString(6, mb.getEMAIL());
			pstmt.setString(7, mb.getMOBILE());
			pstmt.setString(8, mb.getPHONE());
			pstmt.setString(9, mb.getZIPCODE());
			pstmt.setString(10, mb.getADDR1());
			pstmt.setString(11, mb.getADDR2());
			pstmt.setTimestamp(12, mb.getJOIN_DATE());
			pstmt.executeUpdate();
			
			return true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close();}

		}
		
		return false;
	}
	
	public int userCheck(String id, String pw) throws SQLException{
		String sql=null;
		int x=-1;
		
		try{
			sql="select PW from member where ID=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				String memberpw=rs.getString("PW");
				
				if(memberpw.equals(pw)){
					x=1;
				}else{
					x=0;
				}
			}else{
				x=-1;
			}
			
			return x;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			if(rs != null){ rs.close(); }
			
		}
		
		return -1;
	}
	
	public int confirmId(String id) throws SQLException{
		String sql=null;
		int x=-1;
		
		try{
			sql="select ID from member where ID=? ";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				x=1;
			}else{
				x=-1;
			}
			
			return x;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			if(rs != null){ rs.close(); }
			
		}
		
		return -1;
	}
	
	public MemberBean getMember(String id) throws SQLException{
		MemberBean member=null;
		String sql=null;
		
		try{
			sql="select * from member where ID=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				member=new MemberBean();
				
				member.setID(rs.getString("ID"));
				member.setNAME(rs.getString("NAME"));
				member.setJUMIN1(rs.getInt("JUMIN1"));
				member.setJUMIN2(rs.getInt("JUMIN2"));
				member.setEMAIL(rs.getString("EMAIL"));
				member.setMOBILE(rs.getString("MOBILE"));
				member.setPHONE(rs.getString("PHONE"));
				member.setZIPCODE(rs.getString("ZIPCODE"));
				member.setADDR1(rs.getString("ADDR1"));
				member.setADDR2(rs.getString("ADDR2"));
				
				return member;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			if(rs != null){ rs.close(); }
			
		}
		
		return null;
	}
	
	public void updateMember(MemberBean mb) throws SQLException{
		String sql=null;
		
		try{
			
			sql="update memeber set PW=?, NAME=?,"+
			"EMAIL=?, MOBILE=?"+ "PHONE=? ZIPCODE=?, ADDR1=?,"+
					"ADDR2=? where ID=?";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, mb.getPW());
			pstmt.setString(2, mb.getNAME());
			pstmt.setString(3, mb.getEMAIL());
			pstmt.setString(4, mb.getMOBILE());
			pstmt.setString(5, mb.getPHONE());
			pstmt.setString(6, mb.getZIPCODE());
			pstmt.setString(7, mb.getADDR1());
			pstmt.setString(8, mb.getADDR2());
			pstmt.setString(9, mb.getID());			
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close();}
			
		}
	}
	
	public int deleteMember(String id, String pw) throws SQLException{
		String sql=null;
		int x=-1;
		
		try{
			sql="select PW from member where ID=?";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				String memberpw=rs.getString("PW");
				if(memberpw.equals(pw)){
					sql="delete from member where ID=?";
					pstmt=con.prepareStatement(sql);
					pstmt.setString(1, id);
					pstmt.executeUpdate();
					x=1;
				}else{
					x=0;
				}
				
				return x;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			
		}
		
		return -1;
	}	
	
	public MemberBean findId(String name, String jumin1, String jumin2)
	throws SQLException{
		String sql=null;
		MemberBean member=new MemberBean();
		
		try{
			sql="select ID, PW, JUMIN1,"+ "JUMIN2 from member where NAME=?";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, name);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				String memberjumin1=rs.getString("JUMIN1");
				String memberjumin2=rs.getString("JUMIN2");
				
				if(memberjumin1.equals(jumin1) && 
						memberjumin2.equals(jumin2)){
					member.setID(rs.getString("ID"));
					member.setPW(rs.getString("PW"));
					
					return member;
				}
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			if(rs != null){ rs.close(); }
			
		}
		
		return null;
	}
	
	public boolean isAdmin(String id){
		String sql="select ADMIN from MEMBER where ID=?";
		int admin=0;
		
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs=pstmt.executeQuery();
			rs.next();
			
			admin=rs.getInt("ADMIN");
			
			if(admin==1){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	public List searchZipcode(String searchdong){
		String sql="select * from zipcode where dong like ?";
		List zipcodeList=new ArrayList();
		
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, "%"+searchdong+"%");
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				String sido=rs.getString("sido");
				String gugun=rs.getString("gugun");
				String dong=rs.getString("dong");  
				String ri=rs.getString("ri"); 
				String bunji=rs.getString("bunji");
				if(ri == null) ri="";
				if(bunji == null) bunji="";
				
				String zipcode=rs.getString("zipcode");
				String addr=sido+ " "+gugun+ " "+dong+ " "+ri+ " "+bunji;
				
				zipcodeList.add(zipcode+","+addr);
			}
			
			return zipcodeList;
		} catch (SQLException e){
			e.printStackTrace();
		}
		
		return null;
	}
}
